<?php
namespace core\db;

use mysqli_result;

class Db {

    //保存全局实例
    private static $instance;

    private $_conn;
    private $_where;
    private $_table;
    private $_filed;
    private $_alias;
    private $_order;
    private $_limit;
    private $_group;
    private $_join;

    private function __construct()
    {
        $config = require_once APP_PATH.'config/mysql.php';
        $config = $config['pool_list']['base'];
        $link = mysqli_connect($config['host'].":".$config['port'], $config['username'], $config['password']);
        if (!$link) {
            echo "连接数据库失败";
            exit;
        }
        mysqli_select_db($link, $config['dbname']);
        $this->_conn = $link;
    }

    /**
     * 指定数据表名
     * @param $table
     * @return Db
     */
    public static function name($table)
    {
        if(!(self::$instance instanceof self))
        {
            self::$instance = new self();
        }
        self::$instance->table("$table");
        return self::$instance;
    }

    /**
     * 指定数据表
     * @param $table
     * @return $this
     */
    public function table($table)
    {
        $this->_table = $table;
        return $this;
    }

    /**
     * 查询指定的字段
     * @param $filed
     * @return $this
     */
    public function field($filed)
    {
        $this->_filed = $filed;
        return $this;
    }

    /**
     * 表名起别名
     * @param $name
     * @return $this
     */
    public function alias($name)
    {
        $this->_alias = $name;
        return $this;
    }

    /**
     * where条件
     * @param $field
     * @param $exp
     * @param $val
     * @return $this
     */
    public function where($field, $exp, $val)
    {
        if ($exp == 'in'){
            if (!empty($this->_where)){
                $this->_where .= " and ".$field.' '.$exp.' '."($val)";
            }else{
                $this->_where = "where ".$field.' '.$exp.' '."($val)";
            }
        }else{
            if (!empty($this->_where)){
                $this->_where .= " and ".$field.' '.$exp.' '."'$val'";
            }else{
                $this->_where = "where ".$field.' '.$exp.' '."'$val'";
            }
        }
        return $this;
    }

    /**
     * 排序数据
     * @param $field
     * @param string $exp
     * @return $this
     */
    public function order($field, $exp = 'asc')
    {
        $str = "order by ".$field." $exp";
        $this->_order = $str;
        return $this;
    }

    /**
     * 限制数据条数
     * @param $number
     * @return $this
     */
    public function limit($number)
    {
        $str = "limit $number";
        $this->_limit = $str;
        return $this;
    }

    /**
     * 分组数据
     * @param $filed
     * @return $this
     */
    public function group($filed)
    {
        $str = "";
        if (is_array($filed)){
            $filedStr = implode(',',$filed);
            $str = "group by $filedStr";
        }elseif (is_string($filed)&&!empty($filed)){
            $str = "group by $filed";
        }
        $this->_group = $str;
        return $this;
    }

    /**
     * 连接表
     * @param $table
     * @param $exp
     * @return $this
     */
    public function join($table, $exp)
    {
        $joinStr = "join $table on $exp";
        $this->_join = $joinStr;
        return $this;
    }

    /**
     * 查询多条数据
     * @return array|void
     */
    public function select()
    {
        if ($this->_filed == null || empty($this->_filed)){
            $this->_filed = '*';
        }
        $sql = "select {$this->_filed} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeAll($sql);
    }

    /**
     * 查询单条数据
     * @return array|false|string[]|void|null
     */
    public function find()
    {
        if ($this->_filed == null || empty($this->_filed)){
            $this->_filed = '*';
        }
        $sql = "select {$this->_filed} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeOne($sql);
    }

    /**
     * 查询单个字段
     * @param $filed
     * @return false|mixed|string
     */
    public function value($filed)
    {
        $sql = "select {$filed} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        $data = $this->executeOne($sql);
        return $data ? $data[$filed] : false;
    }

    /**
     * 删除数据
     * @return bool|mysqli_result
     */
    public function delete(){
        $sql = "DELETE FROM `{$this->_table}` {$this->_where}";
        $this->clearParam();
        return mysqli_query($this->_conn,$sql);
    }

    /**
     * 插入数据
     * @param $data
     * @return bool|mysqli_result
     */
    public function insert($data)
    {
        if(is_array($data)){
            $keys = '';
            $values = '';
            foreach ($data as $key => $value) {
                $keys .= "`".$key."`,";
                $values .= "'".$value."',";
            }
            $keys = rtrim($keys,',');
            $values = rtrim($values,',');
        }
        $sql = "INSERT INTO `{$this->_table}`({$keys}) VALUES({$values})";
        $this->clearParam();
        return mysqli_query($this->_conn,$sql);
    }

    /**
     * 添加数据并返回id值
     * @param $data
     * @return int|string
     */
    public function insertGetLastId($data)
    {
        if(is_array($data)){
            $keys = '';
            $values = '';
            foreach ($data as $key => $value) {
                $keys .= "`".$key."`,";
                $values .= "'".$value."',";
            }
            $keys = rtrim($keys,',');
            $values = rtrim($values,',');
        }
        $sql = "INSERT INTO `{$this->_table}`({$keys}) VALUES({$values})";
        $this->clearParam();
        mysqli_query($this->_conn,$sql);
        return mysqli_insert_id($this->_conn);
    }

    /**
     * 更新数据
     * @param $data
     * @return bool|mysqli_result
     */
    public function update($data){
        $str = '';
        if(is_array($data)){
            foreach ($data as $key => $value) {
                $str .= "$key = '{$value}',";
            }
            //截取字符串最后一位
            $str = mb_substr($str,0,mb_strlen($str)-1);
        }
        $sql = "update {$this->_table} set {$str} {$this->_where}";
        $this->clearParam();
        return mysqli_query($this->_conn,$sql);
    }

    /**
     * 某个字段的值自增
     * @param $field
     * @param int $step
     * @return bool|mysqli_result
     */
    public function setInc($field, $step = 1){
        $sql = "update {$this->_table} set {$field} = {$field} + {$step} {$this->_where}";
        $this->clearParam();
        return mysqli_query($this->_conn,$sql);
    }

    /**
     * 某个字段的值自减
     * @param $field
     * @param int $step
     * @return bool|mysqli_result
     */
    public function setDec($field, $step = 1){
        $sql = "update {$this->_table} set {$field} = {$field} - {$step} {$this->_where}";
        $this->clearParam();
        return mysqli_query($this->_conn,$sql);
    }

    /**
     * 查询某个字段的最大值
     * @param string $field
     * @param string $alias
     * @return array|false|string[]|void|null
     */
    public function max($field = '*', $alias = '')
    {
        if ($field != '*'){
            $field = "max({$field})";
        }
        $sql = "select {$field} {$alias} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeOne($sql);
    }

    /**
     * 查询某个字段的最小值
     * @param string $field
     * @param string $alias
     * @return array|false|string[]|void|null
     */
    public function min($field = '*', $alias = '')
    {
        if ($field != '*'){
            $field = "min({$field})";
        }
        $sql = "select {$field} {$alias} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeOne($sql);
    }

    /**
     * 查询某个字段的总和
     * @param string $field
     * @param string $alias
     * @return array|false|string[]|void|null
     */
    public function sum($field = '*', $alias = '')
    {
        if ($field != '*'){
            $field = "sum({$field})";
        }
        $sql = "select {$field} {$alias} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeOne($sql);
    }

    /**
     * 查询某个字段的平均数
     * @param string $field
     * @param string $alias
     * @return array|false|string[]|void|null
     */
    public function avg($field = '*', $alias = '')
    {
        if ($field != '*'){
            $field = "avg({$field})";
        }
        $sql = "select {$field} {$alias} from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        return $this->executeOne($sql);
    }

    /**
     * 查询总条数
     * @return mixed|string
     */
    public function count()
    {
        $sql = "select count(*) as number from {$this->_table} {$this->_alias} {$this->_join} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
        $this->clearParam();
        $data = $this->executeOne($sql);
        return $data['number'];
    }

    /**
     * 直接执行 查询 SQL 语句
     * @param $sql
     * @return array|void
     */
    public static function query($sql)
    {
        $obj = new Db();
        $obj->clearParam();
        return $obj->executeAll($sql);
    }

    /**
     * 直接执行 更新和写入数据 SQL 语句
     * @param $sql
     * @return bool|mysqli_result
     */
    private static function execute($sql)
    {
        $obj = new Db();
        $obj->clearParam();
        return mysqli_query($obj->_conn,$sql);
    }

    /**
     * 查询多条
     * @param $sql
     * @return array|void
     */
    private function executeAll($sql)
    {
        $result = mysqli_query($this->_conn,$sql);
        if ($result == false){
            return ;
        }
        $this->clearParam();
        $arr = [];
        while ($row = mysqli_fetch_assoc($result)){
            $arr[] = $row;
        }
        return $arr;
    }

    /**
     * 查询一条
     * @param $sql
     * @return array|false|string[]|void|null
     */
    private function executeOne($sql)
    {
        $result = mysqli_query($this->_conn,$sql);
        if ($result == false){
            return ;
        }
        $this->clearParam();
        return mysqli_fetch_assoc($result);
    }

    /**
     * 清除条件属性
     */
    private function clearParam()
    {
        $this->_where = null;
        $this->_join = null;
        $this->_filed = null;
        $this->_group = null;
        $this->_order = null;
        $this->_alias = null;
        $this->_table = null;
        $this->_limit = null;
    }
}

